package edu.ucsd.cse135.gas.logic.chair;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.HashMap;

import edu.ucsd.cse135.gas.logic.applicant.Applicant;
import edu.ucsd.cse135.gas.logic.support.Database;

public class Applications {

	/** Method to get the overview needed for applications.jsp page **/
	
	public static ArrayList<HashMap<String, String>> getOverview() {
		
		try {
			
			Connection conn = Database.open();
		
			String sql = "SELECT applicant.name, applicant.first_name, applicant.id " + 
						 "FROM applicant;";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			ResultSet rs = preStmt.executeQuery();
			
			ArrayList<HashMap<String, String>> al = new ArrayList<HashMap<String, String>>();
			
			/** Iterate over result set to get all the data and put them into a hash map 
			 *  and put that hash map into an array list to be returned **/
			
			while(rs.next())
			{
				HashMap<String, String> hm = new HashMap<String, String>();
				
				hm.put("name", rs.getString("name"));
				hm.put("first_name", rs.getString("first_name"));
				hm.put("id", rs.getString("id"));
				
				al.add(hm);
			}
			
			Database.close();
			
			return al;
			
		}
		catch(Exception e)
		{
			return null;
		}
	}

	/** Method to get the different disciplines **/
	
	public static ArrayList<HashMap<String, String>> getDisciplines() {
		
		try {
			
			Connection conn = Database.open();
			
			String sql = "SELECT discipline.id, discipline.name, COUNT(applicant.name) AS count " +
						 "FROM (applicant FULL JOIN degree " +
						 "ON degree.applicant = applicant.id) " +
						 "FULL JOIN discipline " +
						 "ON degree.discipline = discipline.id " +
						 "GROUP BY discipline.name, discipline.id " +
						 "ORDER BY discipline.id;";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			
			ResultSet rs = preStmt.executeQuery();
			
			ArrayList<HashMap<String, String>> al = new ArrayList<HashMap<String, String>>();
			
			while(rs.next())
			{
				HashMap<String, String> hm = new HashMap<String, String>();
				
				hm.put("id", rs.getString("id"));
				hm.put("discipline", rs.getString("name"));
				hm.put("count", rs.getString("count"));
				
				al.add(hm);
			}
			
			rs.close();
			preStmt.close();
			Database.close();
			
			return al;
			
		}
		catch(Exception e)
		{
			Database.close();
			return null;
		}
	}

	/** Method to get all users who have the chosen discipline **/
	
	public static ArrayList<HashMap<String, String>> getDisciplinesUsers(int disciplineId)
	{
		try {
			
			Connection conn = Database.open();
			
			String sql = "SELECT DISTINCT applicant.name, applicant.first_name, applicant.mail " +
						 "FROM applicant, degree " +
						 "WHERE applicant.id = degree.applicant AND degree.discipline = ? ;";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			preStmt.setInt(1, disciplineId);
			
			ResultSet rs = preStmt.executeQuery();
			
			ArrayList<HashMap<String, String>> al = new ArrayList<HashMap<String, String>>();
			
			while(rs.next())
			{
				HashMap<String, String> hm = new HashMap<String, String>();
				
				hm.put("mail", rs.getString("mail"));
				hm.put("name", rs.getString("name"));
				hm.put("firstName", rs.getString("first_name"));
				
				al.add(hm);
			}
			
			rs.close();
			preStmt.close();
			Database.close();
			
			return al;
			
		}
		catch(Exception e)
		{
			Database.close();
			return null;
		}
	}
	
	/** Method for visualization of Disciplines **/
	
	public static String visualizeDisciplines() {
	
		StringBuffer chartData = new StringBuffer();
		
		chartData.append("[");
		
		for (int i = 0; i < Applications.getDisciplines().size(); i++) {
			
			chartData.append("['" + Applications.getDisciplines().get(i).get("discipline") + 
					"', " + Applications.getDisciplines().get(i).get("count") + "],\n");
		}

		chartData.deleteCharAt(chartData.length() - 2);
		chartData.append("]");

		return chartData.toString();
	}
	
	/** Method to get all specializations **/
	
	public static ArrayList<HashMap<String, String>> getSpecializations() {
		
		try {
			
			Connection conn = Database.open();
			
			String sql = "SELECT specialization.id, specialization.name, COUNT(applicant.name) AS count " +
						 "FROM applicant FULL JOIN specialization " +
						 "ON applicant.specialization = specialization.id " +
						 "GROUP BY specialization.name, specialization.id " +
						 "ORDER BY specialization.id;";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			
			ResultSet rs = preStmt.executeQuery();
			
			ArrayList<HashMap<String, String>> al = new ArrayList<HashMap<String, String>>();
			
			while(rs.next())
			{
				HashMap<String, String> hm = new HashMap<String, String>();
				
				hm.put("id", rs.getString("id"));
				hm.put("specialization", rs.getString("name"));
				hm.put("count", rs.getString("count"));
				
				al.add(hm);
			}
			
			rs.close();
			preStmt.close();
			Database.close();
			
			return al;
			
		}
		catch(Exception e)
		{
			Database.close();
			return null;
		}
	}		

	/** Method to get all users with specific specialization **/
	
	public static ArrayList<HashMap<String, String>> getSpecializationsUsers(int specializationId)
	{
		try {
			
			Connection conn = Database.open();
			
			String sql = "SELECT applicant.mail, applicant.name, applicant.first_name FROM applicant " +
						 "WHERE applicant.specialization = ? ;";
			
			PreparedStatement preStmt = conn.prepareStatement(sql);
			preStmt.setInt(1, specializationId);
			
			ResultSet rs = preStmt.executeQuery();
			
			ArrayList<HashMap<String, String>> al = new ArrayList<HashMap<String, String>>();
			
			while(rs.next())
			{
				HashMap<String, String> hm = new HashMap<String, String>();
				
				hm.put("mail", rs.getString("mail"));
				hm.put("name", rs.getString("name"));
				hm.put("firstName", rs.getString("first_name"));
				
				al.add(hm);
			}
			
			rs.close();
			preStmt.close();
			Database.close();
			
			return al;
			
		}
		catch(Exception e)
		{
			Database.close();
			return null;
		}
	}
	
	/** Method for visualization of Specializations **/
	
	public static String visualizeSpecializations() {
	
		StringBuffer chartData = new StringBuffer();
		
		chartData.append("[");
		
		for (int i = 0; i < Applications.getSpecializations().size(); i++) {
			
			chartData.append("['" + Applications.getSpecializations().get(i).get("specialization") + 
					"', " + Applications.getSpecializations().get(i).get("count") + "],\n");
		}

		chartData.deleteCharAt(chartData.length() - 2);
		chartData.append("]");

		return chartData.toString();
	}

	//TODO User profile page
	
	public static Applicant getUserProfile(int applicantId) {
		return null;
	}

}